﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
using System.Data;
using System.Text.RegularExpressions;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace BoletoElectronicoDesktop
{


    public static class BeneficiarioDAO
    {


        //devuelve la cantidad de beneficiarios con la misma razon social q la ingresada
        static public int ExisteRazonSocial(string razon)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Beneficiario_Filtro";
            command.CommandTimeout = 10;
            command.Parameters.AddWithValue("@razon_social", razon);
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return Convert.ToInt32(dataTable.Rows.Count);
        }

        static public int ExisteRazonSocialDiferente(string id,string razon)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Beneficiario_RazonSocalDiferente";
            command.CommandTimeout = 10;
            command.Parameters.AddWithValue("@razon_social", razon);
            command.Parameters.AddWithValue("@id", id);
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return Convert.ToInt32(dataTable.Rows[0][0]);
        }
        static public DataTable ObtenerRubros()
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Rubro_Filtro";
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;

        }
        static public void InsertarBeneficiario(string razon, string calle, string numero, string piso, string depto, string idRubro)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Beneficiario_Insercion";
            command.Parameters.AddWithValue("@razon_social", razon);
            command.Parameters.AddWithValue("@calle", calle);
            command.Parameters.AddWithValue("@numero", numero);
            if (string.Compare(piso, "") != 0)
            {
                command.Parameters.AddWithValue("@piso", piso);
            }
            if (string.Compare(depto, "") != 0)
            {
                command.Parameters.AddWithValue("@departamento ", depto);
            }
            command.Parameters.AddWithValue("@rubro", idRubro);
            command.CommandTimeout = 10;
            command.ExecuteReader();
            connection.Close();
        }

        public static DataTable ObtenerMarcas()
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Postnet_Marcas";
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;
        }

        public static DataTable ObtenerModelos(string idMarca)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Postnet_Modelos";
            command.Parameters.AddWithValue("@idMarca", idMarca);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;
        }
        //obtiene el id del beneficiario recién creado
        public static string ObtenerId(string razon)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Beneficiario_Filtro";
            command.Parameters.AddWithValue("@razon_social", razon);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable.Rows[0][0].ToString();

        }

        public static void InsertarPostnet(string numero, string idBeneficiario, string idModelo)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Postnet_Insercion";
            command.Parameters.AddWithValue("@numero", numero);
            command.Parameters.AddWithValue("@beneficiario", idBeneficiario);
            command.Parameters.AddWithValue("@modelo", idModelo);
            command.Parameters.AddWithValue("@habilitado", "True");
            command.CommandTimeout = 10;
            command.ExecuteReader();
            connection.Close();
        }

        public static int ExistePostnet(string numero)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].[Postnet_Filtro]";
            command.CommandTimeout = 10;
            command.Parameters.AddWithValue("@numero_serie", numero);
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return Convert.ToInt32(dataTable.Rows.Count);

        }

        public static int ExistePostnetHabilitado(string numero)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].[Postnet_Filtro]";
            command.CommandTimeout = 10;
            command.Parameters.AddWithValue("@numero_serie", numero);
            command.Parameters.AddWithValue("@habilitado", "True");
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return Convert.ToInt32(dataTable.Rows.Count);

        }
        public static int ExistePostnetInhabilitado(string numero)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].[Postnet_Filtro]";
            command.CommandTimeout = 10;
            command.Parameters.AddWithValue("@numero_serie", numero);
            command.Parameters.AddWithValue("@habilitado", "False");
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return Convert.ToInt32(dataTable.Rows.Count);

        }
        public static int ExistePostnetInhabilitadoConModelo(string numero, string idModelo)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].[Postnet_Filtro]";
            command.CommandTimeout = 10;
            command.Parameters.AddWithValue("@numero_serie", numero);
            command.Parameters.AddWithValue("@modelo", idModelo);
            command.Parameters.AddWithValue("@habilitado", "False");
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return Convert.ToInt32(dataTable.Rows.Count);

        }
        public static int ExistePostnetHabilitadoConModelo(string numero, string idModelo)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].[Postnet_Filtro]";
            command.CommandTimeout = 10;
            command.Parameters.AddWithValue("@numero_serie", numero);
            command.Parameters.AddWithValue("@modelo", idModelo);
            command.Parameters.AddWithValue("@habilitado", "True");
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return Convert.ToInt32(dataTable.Rows.Count);

        }
        public static DataTable ObtenerPostnetsHabilitados(string numero, string modelo, string marca)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Postnet_Marcas_Filtro";
            if (numero != "")
                command.Parameters.AddWithValue("@numero_serie", numero);
            if (modelo != "")
                command.Parameters.AddWithValue("@modelo", modelo);
            if (marca != "")
                command.Parameters.AddWithValue("@idMarca", marca);
            command.Parameters.AddWithValue("@habilitado", "True");
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;
        }
        public static DataTable ObtenerBeneficiariosHabilitados(string razon, string calle, string num, string piso, string depto, string postnet, string rubro)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].[Beneficiario_Postnet_Filtro]";
            if (razon != "")
                command.Parameters.AddWithValue("@razon_social", razon);
            if (calle != "")
                command.Parameters.AddWithValue("@calle", calle);
            if (num != "")
                command.Parameters.AddWithValue("@numero_calle", num);
            if (piso != "")
                command.Parameters.AddWithValue("@piso", piso);
            if (depto != "")
                command.Parameters.AddWithValue("@departamento", depto);
            if (postnet != "")
                command.Parameters.AddWithValue("@postnet", postnet);
            if (rubro != "")
                command.Parameters.AddWithValue("@rubro_id", rubro);
            command.Parameters.AddWithValue("@habilitado", "True");
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;
        }
        public static DataTable ObtenerBeneficiarios(string razon, string calle, string num, string piso, string depto, string postnet, string rubro)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].[Beneficiario_Postnet_Filtro]";
            if (razon != "")
                command.Parameters.AddWithValue("@razon_social", razon);
            if (calle != "")
                command.Parameters.AddWithValue("@calle", calle);
            if (num != "")
                command.Parameters.AddWithValue("@numero_calle", num);
            if (piso != "")
                command.Parameters.AddWithValue("@piso", piso);
            if (depto != "")
                command.Parameters.AddWithValue("@departamento", depto);
            if (postnet != "")
                command.Parameters.AddWithValue("@postnet", postnet);
            if (rubro != "")
                command.Parameters.AddWithValue("@rubro_id", rubro);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;
        }
        //devuelve los datos del beneficiario pedido
        public static DataTable ObtenerBeneficiario(string id)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].[Beneficiario_Filtro]";
            command.Parameters.AddWithValue("@id", id);
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;
        }

        //devuelve los datos del beneficiario pedido
        public static DataTable ObtenerPostnetsHabilitadosBeneficiario(string id)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].[Postnet_Marcas_Filtro]";
            command.Parameters.AddWithValue("@beneficiario", id);
            command.Parameters.AddWithValue("@habilitado", "True");
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;
        }

        public static void InhabilitarBeneficiario(string beneficiario)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].[Beneficiario_Dar_Baja]";
            command.Parameters.AddWithValue("@id", beneficiario);
            command.CommandTimeout = 10;
            command.ExecuteReader();
            connection.Close();
        }
        public static void ModificarBeneficiario(string id, string razon, string calle, string numero, string piso, string depto, string rubro, string hab)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].[Beneficiario_Modificacion]";
            command.Parameters.AddWithValue("@razon_social", razon);
            command.Parameters.AddWithValue("@calle", calle);
            command.Parameters.AddWithValue("@numero_calle", numero);
            if (piso !="")
                command.Parameters.AddWithValue("@piso", piso);
            if(depto != "")
                command.Parameters.AddWithValue("@departamento", depto);
            command.Parameters.AddWithValue("@rubro", rubro);
            command.Parameters.AddWithValue("@habilitado", hab);
            command.Parameters.AddWithValue("@id", id);
            command.CommandTimeout = 10;
            command.ExecuteReader();
            connection.Close();
        }

        public static void DarDeBajaPostnets(string id)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].[Beneficiario_BajaPostnets]";
            command.Parameters.AddWithValue("@id", id);
            command.CommandTimeout = 10;
            command.ExecuteReader();
            connection.Close();
        }

        public static void HabilitarPostnet(string id)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].[Postnet_DarAlta]";
            command.Parameters.AddWithValue("@idPostnet", id);
            command.CommandTimeout = 10;
            command.ExecuteReader();
            connection.Close();
        }

        public static int EsPostnetPropio(string idBeneficiario, string numeroPostnet)
        {
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].[Postnet_Filtro]";
            command.CommandTimeout = 10;
            command.Parameters.AddWithValue("@numero_serie", numeroPostnet);
            command.Parameters.AddWithValue("@beneficiario", idBeneficiario);
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return Convert.ToInt32(dataTable.Rows.Count);

        }

    }//fin clase
}//fin namespace